package Sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map.Entry;

public class operateSql {
	String sqlURL;
	String userName;
	String passWord;
	String databaseName;
	
	public static void main(String[] args) throws Exception {
		
	}

	public operateSql(String sqlURL,String userName,String passWord,String databaseName){
		this.sqlURL=sqlURL;
		this.userName=userName;
		this.passWord=passWord;
		this.databaseName=databaseName;
	}
	
	/**创建表
	 * 
	 * @param databaseName：数据库名称
	 * @param tableName：表的名称
	 * @param tableKV：表的字段名和字段的属性 Map集合
	 * @return
	 * @throws Exception
	 */
	
	public boolean createTable(String tableName,HashMap<String,String> tableKV){
		StringBuffer sb=new StringBuffer();
		for (Entry<String, String> entry : tableKV.entrySet()) {
			sb.append(entry.getKey()+" "+entry.getValue()+","+"\n");
		}
		ArrayList<String> ls=runCmdSql(sqlURL+"/"+databaseName,"show tables;");
		if(ls.contains(databaseName+" ")){
			runCmdSql(sqlURL+"/"+databaseName,"drop table "+tableName+";");
		}else{
			runCmdSql(sqlURL+"/"+databaseName,"create table "+tableName+"("+"\n"+
							"id int not null auto_increment comment\"id\","+"\n"+
							sb.toString()+
							"primary key(id)"+"\n"
							+")");
		}
		runCmdSql(sqlURL+"/"+databaseName,"desc "+tableName+" ;");
		return true;
	}
	
	/**创建数据库
	 * 存在数据库则删除后再创建
	 * @param conn 数据库的数型://ip:port
	 * @param databaseName 库名
	 * @return true创建成功，false 创建失败
	 * @throws Exception 
	 */
	public boolean createDatabase(String databaseName){
		ArrayList<String> ls=runCmdSql(sqlURL,"show databases;");
		if(ls.contains(databaseName+" ")){
			runCmdSql(sqlURL,"drop database "+databaseName+";");
		}
		runCmdSql(sqlURL,"create database "+databaseName+" character set utf8 collate utf8_general_ci;");
		runCmdSql(sqlURL,"use "+databaseName+";");
		ls.clear();
		ls=runCmdSql(sqlURL,"select database();");
		if(!ls.contains(databaseName)) return false;
		return true;
	}
	
	/**
	 * 连接数据库
	 * @param url：数据连接的IP:端口
	 * @param username：用户名
	 * @param password：密码
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @throws Exception
	 */
    public  Connection connectSql(String url,String username,String password){
        Connection conn=null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return conn;
    }
    
    /**
     * 发送SQL命令获取返回值
     * @param conn：SQL连接对象
     * @param sql：SQL命令
     * @return
     */
    public ArrayList<String> runCmdSql(String url,String sql){
    	Connection conn = connectSql(url,userName,passWord);
    	ArrayList<String> dataList= new ArrayList<String>();;
		ResultSet rs = null;
		Statement st=null;
		try {
			st = conn.createStatement();
			boolean hasResultSet = st.execute(sql);
	        st.addBatch(sql);
	        if (hasResultSet) {
	            rs = st.getResultSet();
	            ResultSetMetaData rsmd = rs.getMetaData();
	            int columnCount = rsmd.getColumnCount();
            	StringBuffer sb=new StringBuffer();
	            while (rs.next()) {
	            	sb.delete(0,sb.length());
	                for (int i = 0; i < columnCount; i++) {
	                	String str=rs.getString(i + 1)+" ";
	                    sb.append(str);
	                }
	                //System.out.println(sb.toString());
	               dataList.add(sb.toString());
	            }
	        }else{
	        	dataList.add(""+st.getUpdateCount());
	            //System.out.println("该SQL语句影响的记录有" + st.getUpdateCount() + "条");
	        }
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		System.out.println("sql= "+sql+"  Result=");
		System.out.println();
		//System.out.println(Arrays.toString(dataList.toArray()));
		for(String list:dataList) {
			System.out.println(list.toString());
		}
		disconnectSql(conn,rs,st);
	    return dataList;
    }
    
    
    /**
     * 
     * Description: 关闭资源<br/>
     *
     * @author dingP
     * @param rs
     * @param st
     * @param conn
     */
    public void disconnectSql(Connection conn,ResultSet rs,Statement st) {
        try {
        	if (rs != null)
                rs.close();
            if (st != null)
                st.close();
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
